package cn.edu.sdjzu.cs.dingcan.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.edu.sdjzu.cs.dingcan.entity.Customer;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import cn.edu.sdjzu.cs.dingcan.entity.DinnerTable;
import cn.edu.sdjzu.cs.dingcan.entity.Order;
import cn.edu.sdjzu.cs.dingcan.entity.OrderStatus;
import cn.edu.sdjzu.cs.dingcan.utils.JdbcUtils;
import cn.edu.sdjzu.cs.dingcan.utils.PageBean;


/**
 * Order数据访问类
 * 
 */
public class OrderDao {
	private final Logger logger = LoggerFactory.getLogger(OrderDao.class);

	private final QueryRunner queryRunner;
	private static final String SELECT_BASE_SQL =
			"SELECT o.id, o.customer_id, o.order_time, o.total_price, o.order_status, " +
			"       c.id, c.username, c.real_name, c.phone_number, c.email, c.delivery_address, c.status " +
			"  FROM `order` AS o LEFT JOIN customer AS c ON o.customer_id = c.id ";
	private final ResultSetHandler<Order> orderHandler = new ResultSetHandler<Order>(){
		@Override
		public Order handle(ResultSet rs) throws SQLException {
			if(!rs.next()) {
				return null;
			}
			Order order = new Order();
			order.setId(rs.getLong("id"));
			order.setOrderDate(rs.getDate("order_time"));
			order.setStatus(OrderStatus.values()[rs.getInt("order_status")]);
			order.setTotalPrice(rs.getBigDecimal("total_price"));
			Customer customer = new Customer();
			customer.setId(rs.getLong("customer_id"));
			customer.setUsername(rs.getString("username"));
			customer.setRealName(rs.getString("real_name"));
			customer.setPhoneNumber(rs.getString("phone_number"));
			customer.setEmail(rs.getString("email"));
			customer.setDeliveryAddress(rs.getString("delivery_address"));
			customer.setStatus(Customer.CustomerStatus.values()[rs.getInt("status")]);

			order.setCustomer(customer);
			return order;
		}
	};
	private final ResultSetHandler<List<Order>> orderListHandler = new ResultSetHandler<List<Order>>() {
		@Override
		public List<Order> handle(ResultSet rs) throws SQLException {
			List<Order> result = new ArrayList<>();
			Order order;
			do {
				order = OrderDao.this.orderHandler.handle(rs);
				if(order != null) {
					result.add(order);
				}
			} while(order != null);
			return result;
		}
	};

	public OrderDao() {
		this.queryRunner = JdbcUtils.getQueryRunner();
	}

	/**
	 * 保存新订单
	 * 
	 * @param order 要保存的订单
	 * @return
	 * @throws SQLException
	 */
	public Long add(Order order) throws SQLException {
		String sql = "INSERT `order` (customer_id, order_time, total_price, order_status) VALUES(?, ?, ?, ?)";
		Long orderId;
		ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
		Connection conn = tl.get();
		if(conn == null) {
			orderId = queryRunner.insert(sql, new ScalarHandler<Long>(), 
			order.getCustomer().getId(), order.getOrderDate(), order.getTotalPrice(), order.getStatus().ordinal());
		} else {
			orderId = queryRunner.insert(conn, sql, new ScalarHandler<Long>(), 
			order.getCustomer().getId(), order.getOrderDate(), order.getTotalPrice(), order.getStatus().ordinal());
		}
		return orderId;		
	}

	public void updateOrderStatus(Long orderId, OrderStatus newStatus) throws SQLException {
		String sql = "UPDATE `order` SET order_status = ? WHERE id = ?";
		ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
		Connection conn = tl.get();
		if(conn == null) {
			queryRunner.update(sql, newStatus.ordinal(), orderId);
		} else {
			queryRunner.update(conn, sql, newStatus.ordinal(), orderId);
		}	
	}

	public List<Order> findAll() throws SQLException {
		String sql = SELECT_BASE_SQL;
		return queryRunner.query(sql, this.orderListHandler);
	}

	public Order getOrderById(Long id) throws SQLException {
		String sql = SELECT_BASE_SQL +
					 " WHERE o.id = ?";
		return this.queryRunner.query(sql, this.orderHandler, id);		
	}

	public int getCount() throws SQLException {
		String sql ="SELECT count(*) FROM `order`";
		Long count = queryRunner.query(sql, new ScalarHandler<Long>());
		return count.intValue();		
	}
}
